﻿using Chaotic.DataBase.DataAccess;
using Chaotic.DataBase.DataAccess.Entity;
using Microsoft.Data.Sqlite;
using SQLitePCL;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.Common;
using System.Text.RegularExpressions;
using System.Xml;

namespace Chaotic.DataBase.SqliteAccess
{
    public class SqliteDataAccess : AbstractDataAccess
    {
        private readonly SqliteConnection _connection;
        private SqliteTransaction? _trans;

        public SqliteDataAccess(string connectionString)
        {
            Batteries.Init(); // 初始化
            _connection = new SqliteConnection(connectionString);
        }

        public override DatabaseType DatabaseType => DatabaseType.Sqlite;
        public override IDbConnection DbConnection => _connection;

        public override IDbTransaction? BeginTransaction()
        {
            _trans = _connection.BeginTransaction();
            return _trans;
        }

        private void PrepareCommand(SqliteCommand cmd, CommandType commandType, string? commandText, QueryParameterCollection? commandParameters)
        {
            if (string.IsNullOrEmpty(commandText))
                return;
            //替换的时候需要考虑 引号内的文本问题
            // 正则表达式模式：匹配不在单引号或双引号内的冒号
            string pattern = @":(?=(?:[^']*'[^']*')*[^']*$)(?=(?:[^""]*""[^""]*"")*[^""]*$)"; 
            commandText = Regex.Replace(commandText, pattern, "@");  
            cmd.CommandType = commandType;
            cmd.CommandText = commandText;
            cmd.Connection = _connection;
            cmd.Transaction = _trans;
            if (commandParameters is { Count: > 0 })
            {
                for (int i = 0; i < commandParameters.Count; i++)
                {
                    cmd.Parameters.AddWithValue(commandParameters[i].ParameterName, commandParameters[i].Value);
                    cmd.Parameters[i].Direction = commandParameters[i].Direction;
                }
            }
        }


        public override int ExecuteNonQuery(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataRow row, SqlStruct? sqlStruct)
        {
            int tmpValue = 0;
            LogOutPut(delegate
            {
                SqliteCommand command = new SqliteCommand();
                if (sqlStruct == null)
                {
                    PrepareCommand(command, commandType, commandText, commandParameters);
                }
                else
                {
                    ParamField[] paramFields = sqlStruct.ParamFields;
                    foreach (ParamField paramField in paramFields)
                    {
                        if (paramField.ParamName != null && paramField.ParamName.IndexOf('_') != 1)
                        {
                            commandParameters.Add(paramField.ParamName, row[paramField.FieldName]);
                        }
                        else
                        {
                            commandParameters.Add(paramField.ParamName, row[paramField.FieldName, DataRowVersion.Original]);
                        }
                    }
                    PrepareCommand(command, CommandType.Text, sqlStruct.SqlString, commandParameters);
                }
                tmpValue = command.ExecuteNonQuery();
                command.Parameters.Clear();
                return tmpValue;
            }, "ExecuteNonQuery", commandText, commandParameters);
            return tmpValue;
        }

        public override DataSet? ExecuteDataset(CommandType commandType, string? commandText, QueryParameterCollection commandParameters, DataSet? ds, string tableName)
        {
            LogOutPut(async delegate
            {
                await using SqliteCommand command = new SqliteCommand();
                PrepareCommand(command, commandType, commandText, commandParameters);

                await using SqliteDataReader reader = await command.ExecuteReaderAsync();
                DataTable table = new DataTable(tableName);
                ReadOnlyCollection<DbColumn> columns = await reader.GetColumnSchemaAsync();
                foreach (DbColumn column in columns)
                {
                    if (!table.Columns.Contains(column.ColumnName))
                    {
                        if(column.DataTypeName == "BLOB")
                            table.Columns.Add(column.ColumnName, typeof(string));
                        else
                            table.Columns.Add(column.ColumnName, column.DataType ?? typeof(string));
                    }
                }

                while (reader.Read())
                { 
                    DataRow row = table.NewRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string column = reader.GetName(i);
                        row[column] = reader.GetValue(i);
                    }
                    table.Rows.Add(row);
                }

                ds ??= new DataSet();
                ds.Tables.Add(table);
                command.Parameters.Clear();
                return ds is { Tables.Count: > 0 } ? ds.Tables[0].Rows.Count : 0;
            }, "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override DataSet? ExecuteDataset(string? commandText, QueryParameterCollection commandParameters, DataSet? ds, int startRecord, int maxRecords, string tableName)
        {
            LogOutPut(async delegate
            {
                await using SqliteCommand command = new SqliteCommand();
                PrepareCommand(command, CommandType.Text, commandText, commandParameters);

                await using SqliteDataReader reader = await command.ExecuteReaderAsync();
                DataTable table = new DataTable(tableName);
                ReadOnlyCollection<DbColumn> columns = await reader.GetColumnSchemaAsync();
                foreach (DbColumn column in columns)
                {
                    if (!table.Columns.Contains(column.ColumnName))
                    {
                        table.Columns.Add(column.ColumnName, column.DataType ?? typeof(string));
                    }
                }

                while (reader.Read())
                {
                    DataRow row = table.NewRow();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        string column = reader.GetName(i);
                        row[column] = reader.GetValue(i);
                    }
                    table.Rows.Add(row);
                }

                ds ??= new DataSet();
                ds.Tables.Add(table);
                command.Parameters.Clear();

                return ds is { Tables.Count: > 0 } ? ds.Tables[0].Rows.Count : 0;
            }, "ExecuteDataset", commandText, commandParameters);
            return ds;
        }

        public override IDataReader ExecuteReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            IDataReader dr = null;
            LogOutPut(delegate
            {
                SqliteCommand command = new SqliteCommand();
                PrepareCommand(command, commandType, commandText, commandParameters);
                dr = command.ExecuteReader();
                command.Parameters.Clear();
                return -1;
            }, "ExecuteReader", commandText, commandParameters);
            return dr;
        }

        public override object? ExecuteScalar(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            object? tmpValue = null;
            LogOutPut(delegate
            {
                using SqliteCommand command = new SqliteCommand();
                PrepareCommand(command, commandType, commandText, commandParameters);
                tmpValue = command.ExecuteScalar();
                command.Parameters.Clear();
                return -1;
            }, "ExecuteScalar", commandText, commandParameters);
            return tmpValue;
        }

        public override XmlReader? ExecuteXmlReader(CommandType commandType, string? commandText, QueryParameterCollection commandParameters)
        {
            return null;
        }



        public override void FillingTables(List<TableInfo>? info)
        {
            using IDataReader dataReader = ExecuteReader("SELECT name FROM sqlite_master WHERE type='table'");
            while (dataReader.Read())
            {
                //过滤掉 内部表
                string tableName = dataReader["name"].ToString();
                if (tableName == "sqlite_sequence")
                    continue;

                info.Add(new TableInfo
                {
                    TableName = dataReader["name"].ToString()
                });
            }
        }

        public override void FillingColumns(List<ColumnInfo> info)
        {
            using IDataReader dataReader = ExecuteReader("SELECT name FROM sqlite_master WHERE type='table'");
            while (dataReader.Read())
            {
                string tableName = dataReader["name"].ToString();

                try
                {
                    using IDataReader columnReader = ExecuteReader($"PRAGMA table_info([{tableName}]);");
                    while (columnReader.Read())
                    {
                        info.Add(new ColumnInfo
                        {
                            TableName = tableName,
                            ColumnName = columnReader["name"].ToString(),
                            TypeName = columnReader["type"].ToString(),
                            IsPrimaryKey = Convert.ToBoolean(columnReader["pk"]),
                            IsNull = Convert.ToBoolean(columnReader["notnull"]) == false,
                            DefaultValue = columnReader["dflt_value"] != DBNull.Value ? columnReader["dflt_value"].ToString() : null
                        });
                    }
                }
                catch { }
            }
        }

        public override void FillingIndexs(List<IndexInfo>? info)
        {
            using IDataReader dataReader = ExecuteReader("SELECT * FROM sqlite_master WHERE type='index'");
            while (dataReader.Read())
            { 
                try
                {
                    string tableName = dataReader["tbl_name"].ToString();
                    string indexName = dataReader["name"].ToString();

                    using IDataReader columnReader = ExecuteReader($"PRAGMA index_info([{indexName}]);");
                    while (columnReader.Read())
                    {
                        info.Add(new IndexInfo
                        {
                            ColumnName = columnReader["name"].ToString(),
                            TableName = tableName,
                            IndexName = indexName,
                            Sequence = "",
                            IsUnique = false
                        });
                    }
                }
                catch { }
            }
        }

        public override void FillingRelations(List<RelationshipInfo>? info)
        {
            using IDataReader dataReader = ExecuteReader("SELECT name FROM sqlite_master WHERE type='table'");
            while (dataReader.Read())
            {
                string tableName = dataReader["name"].ToString();

                try
                {
                    using IDataReader columnReader = ExecuteReader($"PRAGMA foreign_key_list([{tableName}]);");
                    while (columnReader.Read())
                    {
                        info.Add(new RelationshipInfo
                        {
                            TableName = tableName,
                            RefKeyName = columnReader["seq"].ToString(),
                            ColumnName = columnReader["from"].ToString(),
                            RelationTableName = columnReader["table"].ToString(),
                            RelationColumnName = columnReader["to"].ToString(),
                            IsCascadingUpdate = columnReader["on_update"].ToString() == "CASCADE",
                            IsCascadingDelete = columnReader["on_delete"].ToString() == "CASCADE"
                        });
                    }
                }
                catch { }
            } 
        }


        public override void FillingViews(List<ViewInfo> info)
        {
           
        }

        public override void FillingProcs(List<ProcInfo> info)
        {
           
        }

        public override void FillingProcParams(List<ProcParamInfo> info)
        {
            
        }

        public override void FillingTypeInfos(List<TypeNameInfos> info)
        {
            info.Add(new TypeNameInfos { TypeName = "INTEGER" });
            info.Add(new TypeNameInfos { TypeName = "REAL" });
            info.Add(new TypeNameInfos { TypeName = "TEXT" });
            info.Add(new TypeNameInfos { TypeName = "DATETIME" });
            info.Add(new TypeNameInfos { TypeName = "BLOB" });
        }

    }
}
